﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using Npgsql;
using Oracle.ManagedDataAccess.Client;
using Dapper.Library.DapperOracle;
using Dapper.Library.DapperPostgreSql;
using Dapper.Library.SqlContainer;
using Dapper.Library.DapperAccess;

namespace Dapper.Library
{
    /// <summary>
    /// 数据库连接实例工厂类
    /// </summary>
    public static class DataBase
    {

        #region 链接

        /// <summary>
        /// 获取连接
        /// </summary>
        /// <returns>打开的链接，如果失败返回的是null</returns>
        public static IDbConnection GetConnection(string databaseName)
        {
            //链接字符串，从工具中读取配置
            string connectionString = string.Empty;
            IDbConnection connection = null;
            try
            {
                DatabaseInstance instance = DatabaseManager.GetDatabase(databaseName);
                connectionString = bool.Parse(instance.IsEncryption) ? AESEncryptHelper.Decrypt(instance.ConnectionString) : instance.ConnectionString;

                if (string.IsNullOrEmpty(connectionString)) return null;

                switch (instance.Type)
                {

                    case DatabaseType.Oracle:
                        connection = new OracleConnection(connectionString.Trim());
                        break;
                    case DatabaseType.PostgreSql:
                        connection = new NpgsqlConnection(connectionString);
                        break;
                    case DatabaseType.MySql:
                        
                        break;
                    case DatabaseType.SqlServer:
                        connection = new SqlConnection(connectionString);
                        break;
                    case DatabaseType.Access:
                        connection = new OleDbConnection(connectionString);
                        break;
                }
                connection.Open();
                return connection;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="connection">数据库连接</param>
        private static void CloseConnection(IDbConnection connection)
        {
            try
            {
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        /// <summary>
        /// 根据sqlConnection名称，获取数据库类型 
        /// sqlConnection.ToString() "Npgsql.NpgsqlConnection"
        /// </summary>
        /// <param name="dataName"></param>
        /// <returns></returns>
        private static DBType GetDatabaseTypeByDataName(IDbConnection sqlConnectionName)
        {

            if (sqlConnectionName.ToString().Equals("Npgsql.NpgsqlConnection"))
            {
                return DBType.PostgreSql;
            }
            else if (sqlConnectionName.ToString().Equals("Oracle.ManagedDataAccess.Client.OracleConnection"))
            {
                return DBType.Oracle;
            }
            else if (sqlConnectionName.ToString().Equals("System.Data.SqlClient.SqlConnection"))
            {
                return DBType.SqlServer;
            }
            else if (sqlConnectionName.ToString().Equals("System.Data.OleDb.OleDbConnection"))
            {
                return DBType.Access;
            }
            else
            {
                return DBType.MySql;
            }
        }

        #region 查询

        /// <summary>
        /// 非事物查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlConnection"></param>
        /// <returns></returns>
        public static IQuerySet<T> QuerySet<T>(this IDbConnection sqlConnection)
        {
            try
            {
                var databaseType = GetDatabaseTypeByDataName(sqlConnection);

                if (databaseType== DBType.PostgreSql){
                    return  new QuerySet<T>(sqlConnection, new PostgreSqlProvider(), null);
                }
                else if (databaseType == DBType.Oracle)
                {
                    return new QuerySet<T>(sqlConnection, new OracleSqlProvider(), null);
                }
                else if (databaseType == DBType.MySql){

                }
                else{
                }
                return new QuerySet<T>(sqlConnection, new PostgreSqlProvider(), null);
            }
            catch (Exception ex)
            {
                CloseConnection(sqlConnection);
                throw ex;
            }
        }

       

        /// <summary>
        /// 事物查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlConnection"></param>
        /// <param name="dbTransaction"></param>
        /// <returns></returns>
        public static IQuerySet<T> QuerySet<T>(this IDbConnection sqlConnection, IDbTransaction dbTransaction = null)
        {
            try
            {
                var databaseType = GetDatabaseTypeByDataName(sqlConnection);

                if (databaseType == DBType.PostgreSql)
                {
                    return new QuerySet<T>(sqlConnection, new PostgreSqlProvider(), dbTransaction);
                }
                else if (databaseType == DBType.Oracle)
                {
                    return new QuerySet<T>(sqlConnection, new OracleSqlProvider(), dbTransaction);
                }
                else if (databaseType == DBType.MySql)
                {
                }
                
                else
                {
                }
                return new QuerySet<T>(sqlConnection, new PostgreSqlProvider(), dbTransaction);
            }
            catch (Exception ex)
            {
                CloseConnection(sqlConnection);
                throw ex;
            }
        }

        /// <summary>
        /// 非事物查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlConnection"></param>
        /// <returns></returns>
        public static QuerySql<T> QuerySql<T>(this IDbConnection sqlConnection)
        {
            try
            {
                var databaseType = GetDatabaseTypeByDataName(sqlConnection);

                if (databaseType == DBType.PostgreSql)
                {
                    return new QuerySql<T>(sqlConnection, new PostgreSqlProvider());
                }
                else if (databaseType == DBType.Oracle)
                {
                    return new QuerySql<T>(sqlConnection, new OracleSqlProvider());
                }
                else if (databaseType == DBType.MySql)
                {
                }
                else
                {
                    return new QuerySql<T>(sqlConnection, new AccessProvider());
                }
                return new QuerySql<T>(sqlConnection, new PostgreSqlProvider());
            }
            catch (Exception ex)
            {
                CloseConnection(sqlConnection);
                throw ex;
            }
        }

        /// <summary>
        /// 事物查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlConnection"></param>
        /// <param name="dbTransaction"></param>
        /// <returns></returns>
        public static QuerySql<T> QuerySql<T>(this IDbConnection sqlConnection, IDbTransaction dbTransaction)
        {
            try
            {
                var databaseType = GetDatabaseTypeByDataName(sqlConnection);

                if (databaseType == DBType.PostgreSql)
                {
                    return new QuerySql<T>(sqlConnection, new PostgreSqlProvider(), dbTransaction);
                }
                else if (databaseType == DBType.Oracle)
                {
                    return new QuerySql<T>(sqlConnection, new OracleSqlProvider(), dbTransaction);
                }
                else if (databaseType == DBType.MySql)
                {
                }
                else
                {
                }
                return new QuerySql<T>(sqlConnection, new PostgreSqlProvider(), dbTransaction);
            }
            catch (Exception ex)
            {
                CloseConnection(sqlConnection);
                throw ex;
            }
        }

        #endregion

        #region 执行

        /// <summary>
        /// 非事物执行，用来解决表达式树不能使用默认参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlConnection"></param>
        /// <returns></returns>
        public static ICommandSet<T> CommandSet<T>(this IDbConnection sqlConnection)
        {
            try
            {
                var databaseType = GetDatabaseTypeByDataName(sqlConnection);

                if (databaseType == DBType.PostgreSql)
                {
                    return new CommandSet<T>(sqlConnection, new PostgreSqlProvider(), null);
                }
                else if (databaseType == DBType.Oracle)
                {
                    return new CommandSet<T>(sqlConnection, new OracleSqlProvider(), null);
                }
                else if (databaseType == DBType.MySql)
                {
                }
                else
                {
                }
                return new CommandSet<T>(sqlConnection, new PostgreSqlProvider(), null);
            }
            catch (Exception ex)
            {
                CloseConnection(sqlConnection);
                throw ex;
            }

        }

        /// <summary>
        /// 事物执行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlConnection"></param>
        /// <param name="dbTransaction"></param>
        /// <returns></returns>
		public static ICommandSet<T> CommandSet<T>(this IDbConnection sqlConnection, IDbTransaction dbTransaction = null)
        {
            try
            {
                var databaseType = GetDatabaseTypeByDataName(sqlConnection);

                if (databaseType == DBType.PostgreSql)
                {
                    return new CommandSet<T>(sqlConnection, new PostgreSqlProvider(), dbTransaction);
                }
                else if (databaseType == DBType.Oracle)
                {
                    return new CommandSet<T>(sqlConnection, new OracleSqlProvider(), dbTransaction);
                }
                else if (databaseType == DBType.MySql)
                {
                }
                else
                {
                }
                return new CommandSet<T>(sqlConnection, new PostgreSqlProvider(), dbTransaction);
            }
            catch (Exception ex)
            {
                CloseConnection(sqlConnection);
                throw ex;
            }
        }

        /// <summary>
        /// 非事物执行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlConnection"></param>
        /// <returns></returns>
        public static CommandSql<T> CommandSql<T>(this IDbConnection sqlConnection)
        {
            try
            {
                var databaseType = GetDatabaseTypeByDataName(sqlConnection);

                if (databaseType == DBType.PostgreSql)
                {
                    return new CommandSql<T>(sqlConnection, new PostgreSqlProvider());
                }
                else if (databaseType == DBType.Oracle)
                {
                    return new CommandSql<T>(sqlConnection, new OracleSqlProvider());
                }
                else if (databaseType == DBType.MySql)
                {
                }
                else
                {
                }
                return new CommandSql<T>(sqlConnection, new PostgreSqlProvider());
            }
            catch (Exception ex)
            {
                CloseConnection(sqlConnection);
                throw ex;
            }
        }

        /// <summary>
        /// 事物执行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlConnection"></param>
        /// <param name="dbTransaction"></param>
        /// <returns></returns>
        public static CommandSql<T> CommandSql<T>(this IDbConnection sqlConnection, IDbTransaction dbTransaction)
        {
            try
            {
                var databaseType = GetDatabaseTypeByDataName(sqlConnection);

                if (databaseType == DBType.PostgreSql)
                {
                    return new CommandSql<T>(sqlConnection, new PostgreSqlProvider(), dbTransaction);
                }
                else if (databaseType == DBType.Oracle)
                {
                    return new CommandSql<T>(sqlConnection, new OracleSqlProvider(), dbTransaction);
                }
                else if (databaseType == DBType.MySql)
                {
                }
                else
                {
                }
                return new CommandSql<T>(sqlConnection, new PostgreSqlProvider(), dbTransaction);
            }
            catch (Exception ex)
            {
                CloseConnection(sqlConnection);
                throw ex;
            }
        }
        #endregion

        /// <summary>
        /// 事物
        /// </summary>
        /// <param name="sqlConnection"></param>
        /// <param name="action"></param>
        /// <param name="exAction"></param>
        public static void Transaction(this IDbConnection sqlConnection, Action<TransContext> action,
            Action<Exception> exAction = null)
        {
            if (sqlConnection.State == ConnectionState.Closed)
                sqlConnection.Open();

            var transaction = sqlConnection.BeginTransaction();
            try
            {
                var databaseType = GetDatabaseTypeByDataName(sqlConnection);

                switch (databaseType)
                {
                    case DBType.SqlServer:
                        break;
                    case DBType.MySql:
                        break;
                    case DBType.Oracle:
                        action(new TransContext(sqlConnection, transaction, new OracleSqlProvider()));
                        break;
                    case DBType.PostgreSql:
                        action(new TransContext(sqlConnection, transaction, new PostgreSqlProvider()));
                        break;
                    default:
                        break;
                }
                transaction.Commit();
            }
            catch (Exception ex)
            {
                if (exAction != null) {
                    exAction(ex);
                } 
                else
                {
                    transaction.Rollback();
                }
                CloseConnection(sqlConnection);
                throw ex;
            }
        }

    }
}
